package com.hao.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.Arrays;

public class POIUtils {
    public static void copySheet(String path, int num) {
        remvoeAllSheet(path);
        Workbook workbook = null;
        try {
            File file = new File(path);
            InputStream is = new FileInputStream(file);
            workbook = WorkbookFactory.create(is);
            Sheet sheet1 = workbook.getSheetAt(0);
            System.out.println(sheet1.getLastRowNum());
            for (int i = 2; i <= num; i++) {
                Sheet newSheet = workbook.createSheet("Sheet" + i);
                copySheet(workbook, sheet1, newSheet, sheet1.getFirstRowNum(), sheet1.getLastRowNum()+1);
            }
            FileOutputStream fos = new FileOutputStream(path);
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 新增sheet，并且复制sheet内容到新增的sheet里
     */
    private static void copySheet(Workbook wb, Sheet fromsheet, Sheet newSheet, int firstrow, int lasttrow) {
        // 复制一个单元格样式到新建单元格
        if ((firstrow == -1) || (lasttrow == -1) || lasttrow < firstrow) {
            return;
        }
        // 复制合并的单元格
        CellRangeAddress region = null;
        for (int i = 0; i < fromsheet.getNumMergedRegions(); i++) {
            region = fromsheet.getMergedRegion(i);
            if ((region.getFirstRow() >= firstrow) && (region.getLastRow() <= lasttrow)) {
                newSheet.addMergedRegion(region);
            }
        }
        Row fromRow = null;
        Row newRow = null;
        Cell newCell = null;
        Cell fromCell = null;
        // 设置列宽
        for (int i = firstrow; i < lasttrow; i++) {
            fromRow = fromsheet.getRow(i);
            if (fromRow != null) {
                for (int j = fromRow.getLastCellNum(); j >= fromRow.getFirstCellNum(); j--) {
                    int colnum = fromsheet.getColumnWidth((short) j);
                    if (colnum > 100) {
                        newSheet.setColumnWidth((short) j, (short) colnum);
                    }
                    if (colnum == 0) {
                        newSheet.setColumnHidden((short) j, true);
                    } else {
                        newSheet.setColumnHidden((short) j, false);
                    }
                }
                break;
            }
        }
        // 复制行并填充数据
        for (int i = 0; i < lasttrow; i++) {
            fromRow = fromsheet.getRow(i);
            if (fromRow == null) {
                continue;
            }
            newRow = newSheet.createRow(i - firstrow);
            newRow.setHeight(fromRow.getHeight());
            for (int j = fromRow.getFirstCellNum(); j < fromRow.getPhysicalNumberOfCells(); j++) {
                fromCell = fromRow.getCell((short) j);
                if (fromCell == null) {
                    continue;
                }
                newCell = newRow.createCell((short) j);
                newCell.setCellStyle(fromCell.getCellStyle());
                int cType = fromCell.getCellType();
                newCell.setCellType(cType);
                switch (cType) {
                    case HSSFCell.CELL_TYPE_STRING:
                        newCell.setCellValue(fromCell.getRichStringCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        newCell.setCellValue(fromCell.getNumericCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_FORMULA:
                        newCell.setCellValue(fromCell.getCellFormula());
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        newCell.setCellValue(fromCell.getBooleanCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_ERROR:
                        newCell.setCellValue(fromCell.getErrorCellValue());
                        break;
                    default:
                        newCell.setCellValue(fromCell.getRichStringCellValue());
                        break;
                }
            }
        }
    }

    public static void remvoeAllSheet(String path) {
        Workbook workbook = null;
        try {
            File file = new File(path);
            InputStream is = new FileInputStream(file);
            workbook = WorkbookFactory.create(is);
            if (workbook.getNumberOfSheets() > 1) {
                for (int i = workbook.getNumberOfSheets() - 1; i > 0; i--) {
                    System.out.println(i);
                    workbook.removeSheetAt(i);
                }
                FileOutputStream fos = new FileOutputStream(path);
                workbook.write(fos);
                fos.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static void main(String[] args) {
        remvoeAllSheet("D:\\jgunitmx.xls");
        Workbook workbook = null;
        try {
            File file = new File("D:\\jgunitmx.xls");
            InputStream is = new FileInputStream(file);
            workbook = WorkbookFactory.create(is);
            Sheet sheet1 = workbook.getSheetAt(0);
            for (int i = 1; i < 10; i++) {
                Sheet newSheet = workbook.createSheet("new" + i);
                copySheet(workbook, sheet1, newSheet, sheet1.getFirstRowNum(), sheet1.getLastRowNum());
            }
            FileOutputStream fos = new FileOutputStream("D:\\jgunitmx.xls");
            workbook.write(fos);
            fos.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
